package com.bojx.report.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.bojx.report.dao.BasicDataDao;
import com.bojx.report.pojo.Constant;
import com.jfinal.aop.Before;
import com.jfinal.kit.PathKit;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.jfinal.upload.UploadFile;

public class ExcelService {
	private static Logger LOG = Logger.getLogger(ExcelService.class);
	private static BasicDataDao dao = new BasicDataDao();
	private static VerifyService verifyService=new VerifyService();
	private Workbook wb;

	/*
	 * 解析excel
	 */
	@Before(Tx.class)
	public Map<String,Object> analyze(UploadFile uploadFile, String organization, int year, int month) throws Exception{
			wb = WorkbookFactory.create(uploadFile.getFile());
			HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);//计算出公式的值
			Sheet sheet = wb.getSheetAt(0);
			//校验表是否是模板提供的数据
			verifyService.verify(sheet,organization,year,month);
			//
			List<Record> branchList = new ArrayList<Record>();
			List<Record> clientList = new ArrayList<Record>();
			// 遍历每一行,读取数据
			List<String> tips=new ArrayList<String>();
			for (int i = 0; i < 6; i++) {
				Record branchRecord = new Record();
				Record clientRecord = new Record();
				Row row = sheet.getRow(i + 4);// 第五行开始，第十行
				String area = (String) getCellValue(row.getCell(0));
				Double branchQty = (Double) getCellValue(row.getCell(1));// excel读取整数变成.0形式
				Double edraftBranchQty = (Double) getCellValue(row.getCell(2));
				Double euserQty = (Double) getCellValue(row.getCell(4));
				Double ebankEuserQty = (Double) getCellValue(row.getCell(5));
				Double draftEuserQty = (Double) getCellValue(row.getCell(6));
				Double edraftEuserQty = (Double) getCellValue(row.getCell(7));
				Double newEdraftEuserQty = (Double) getCellValue(row.getCell(8));
				
				
				branchRecord.set("area", area);
				branchRecord.set("organization", organization);
				branchRecord.set("year", year);
				branchRecord.set("month", month);
				branchRecord.set("branch_qty", branchQty.intValue());
				branchRecord.set("edraft_branch_qty", edraftBranchQty.intValue());
				branchList.add(branchRecord);

				clientRecord.set("area", area);
				clientRecord.set("organization", organization);
				clientRecord.set("year", year);
				clientRecord.set("month", month);
				clientRecord.set("euser_qty", euserQty);
				clientRecord.set("ebank_euser_qty", ebankEuserQty);
				clientRecord.set("draft_euser_qty", draftEuserQty);
				clientRecord.set("edraft_euser_qty", edraftEuserQty);
				clientRecord.set("new_edraft_euser_qty", newEdraftEuserQty);
				clientList.add(clientRecord);
			}

			List<Record> list = new ArrayList<Record>();
			
			// 处理表二数据
			for (int i = 13; i < 25; i++) {
				Row row = sheet.getRow(i);

				String area = null;
				if (i % 2 == 0) {
					area = (String) getCellValue(sheet.getRow(i - 1).getCell(0));
				} else {
					area = (String) getCellValue(row.getCell(0));
				}
				String statisticsType = (String) getCellValue(row.getCell(1));

				// 1电子商业承兑汇票
				// 1.1签发
				Double caEdraftIssueQty = (Double) getCellValue(row.getCell(2));
				Double caEdraftIssueAmt = (Double) getCellValue(row.getCell(3));
				Record caEdraftIssue = new Record();
				caEdraftIssue.set("area", area);
				caEdraftIssue.set("organization", organization);
				caEdraftIssue.set("year", year);
				caEdraftIssue.set("month", month);
				caEdraftIssue.set("bill_type", "caEdraft");
				caEdraftIssue.set("action_type", "issue");
				caEdraftIssue.set("statistics_type", statisticsType);
				caEdraftIssue.set("qty", caEdraftIssueQty.intValue());
				caEdraftIssue.set("amt", caEdraftIssueAmt);
				list.add(caEdraftIssue);

				// 1.2贴现
				Double caEdraftDiscountQty = (Double) getCellValue(row.getCell(4));
				Double caEdraftDiscountAmt = (Double) getCellValue(row.getCell(5));
				Record caEdraftDiscount = new Record();
				caEdraftDiscount.set("area", area);
				caEdraftDiscount.set("organization", organization);
				caEdraftDiscount.set("year", year);
				caEdraftDiscount.set("month", month);
				caEdraftDiscount.set("bill_type", "caEdraft");
				caEdraftDiscount.set("action_type", "discount");
				caEdraftDiscount.set("statistics_type", statisticsType);
				caEdraftDiscount.set("qty", caEdraftDiscountQty);
				caEdraftDiscount.set("amt", caEdraftDiscountAmt);
				list.add(caEdraftDiscount);

				// 1.3转贴现
				Double caEdraftRediscountQty = (Double) getCellValue(row.getCell(6));
				Double caEdraftRediscountAmt = (Double) getCellValue(row.getCell(7));
				Record caEdraftRediscount = new Record();
				caEdraftRediscount.set("area", area);
				caEdraftRediscount.set("organization", organization);
				caEdraftRediscount.set("year", year);
				caEdraftRediscount.set("month", month);
				caEdraftRediscount.set("bill_type", "caEdraft");
				caEdraftRediscount.set("action_type", "reDiscount");
				caEdraftRediscount.set("statistics_type", statisticsType);
				caEdraftRediscount.set("qty", caEdraftRediscountQty);
				caEdraftRediscount.set("amt", caEdraftRediscountAmt);
				list.add(caEdraftRediscount);

				// 2电子银行承兑汇票
				// 2.1签发
				Double baEdraftIssueQty = (Double) getCellValue(row.getCell(8));
				Double baEdraftIssueAmt = (Double) getCellValue(row.getCell(9));
				Record baEdraftIssue = new Record();
				baEdraftIssue.set("area", area);
				baEdraftIssue.set("organization", organization);
				baEdraftIssue.set("year", year);
				baEdraftIssue.set("month", month);
				baEdraftIssue.set("bill_type", "baEdraft");
				baEdraftIssue.set("action_type", "issue");
				baEdraftIssue.set("statistics_type", statisticsType);
				baEdraftIssue.set("qty", baEdraftIssueQty.intValue());
				baEdraftIssue.set("amt", baEdraftIssueAmt);
				list.add(baEdraftIssue);

				// 2.2承兑
				Double baEdraftAcceptQty = (Double) getCellValue(row.getCell(10));
				Double baEdraftAcceptAmt = (Double) getCellValue(row.getCell(11));
				Record baEdraftAccept = new Record();
				baEdraftAccept.set("area", area);
				baEdraftAccept.set("organization", organization);
				baEdraftAccept.set("year", year);
				baEdraftAccept.set("month", month);
				baEdraftAccept.set("bill_type", "baEdraft");
				baEdraftAccept.set("action_type", "accept");
				baEdraftAccept.set("statistics_type", statisticsType);
				baEdraftAccept.set("qty", baEdraftAcceptQty.intValue());
				baEdraftAccept.set("amt", baEdraftAcceptAmt);
				list.add(baEdraftAccept);

				// 2.3贴现
				Double baEdraftDiscountQty = (Double) getCellValue(row.getCell(12));
				Double baEdraftDiscountAmt = (Double) getCellValue(row.getCell(13));
				Record baEdraftDiscount = new Record();
				baEdraftDiscount.set("area", area);
				baEdraftDiscount.set("organization", organization);
				baEdraftDiscount.set("year", year);
				baEdraftDiscount.set("month", month);
				baEdraftDiscount.set("bill_type", "baEdraft");
				baEdraftDiscount.set("action_type", "discount");
				baEdraftDiscount.set("statistics_type", statisticsType);
				baEdraftDiscount.set("qty", baEdraftDiscountQty);
				baEdraftDiscount.set("amt", baEdraftDiscountAmt);
				list.add(baEdraftDiscount);
				// 2.4转贴现
				Double baEdraftRediscountQty = (Double) getCellValue(row.getCell(14));
				Double baEdraftRediscountAmt = (Double) getCellValue(row.getCell(15));
				Record baEdraftRediscount = new Record();
				baEdraftRediscount.set("area", area);
				baEdraftRediscount.set("organization", organization);
				baEdraftRediscount.set("year", year);
				baEdraftRediscount.set("month", month);
				baEdraftRediscount.set("bill_type", "baEdraft");
				baEdraftRediscount.set("action_type", "reDiscount");
				baEdraftRediscount.set("statistics_type", statisticsType);
				baEdraftRediscount.set("qty", baEdraftRediscountQty);
				baEdraftRediscount.set("amt", baEdraftRediscountAmt);
				list.add(baEdraftRediscount);

				// 3.全部商业承兑汇票
				// 3.1签发
				Double caDraftIssueQty = (Double) getCellValue(row.getCell(16));
				Double caDraftIssueAmt = (Double) getCellValue(row.getCell(17));
				Record caDdraftIssue = new Record();
				caDdraftIssue.set("area", area);
				caDdraftIssue.set("organization", organization);
				caDdraftIssue.set("year", year);
				caDdraftIssue.set("month", month);
				caDdraftIssue.set("bill_type", "caDraft");
				caDdraftIssue.set("action_type", "issue");
				caDdraftIssue.set("statistics_type", statisticsType);
				caDdraftIssue.set("qty", caDraftIssueQty.intValue());
				caDdraftIssue.set("amt", caDraftIssueAmt);
				list.add(caDdraftIssue);

				// 3.2贴现
				Double caDraftDiscountQty = (Double) getCellValue(row.getCell(18));
				Double caDraftDiscountAmt = (Double) getCellValue(row.getCell(19));
				Record caDraftDiscount = new Record();
				caDraftDiscount.set("area", area);
				caDraftDiscount.set("organization", organization);
				caDraftDiscount.set("year", year);
				caDraftDiscount.set("month", month);
				caDraftDiscount.set("bill_type", "caDraft");
				caDraftDiscount.set("action_type", "discount");
				caDraftDiscount.set("statistics_type", statisticsType);
				caDraftDiscount.set("qty", caDraftDiscountQty);
				caDraftDiscount.set("amt", caDraftDiscountAmt);
				list.add(caDraftDiscount);
				// 1.3转贴现
				Double caDraftRediscountQty = (Double) getCellValue(row.getCell(20));
				Double caDraftRediscountAmt = (Double) getCellValue(row.getCell(21));
				Record caDdraftRediscount = new Record();
				caDdraftRediscount.set("area", area);
				caDdraftRediscount.set("organization", organization);
				caDdraftRediscount.set("year", year);
				caDdraftRediscount.set("month", month);
				caDdraftRediscount.set("bill_type", "caDraft");
				caDdraftRediscount.set("action_type", "reDiscount");
				caDdraftRediscount.set("statistics_type", statisticsType);
				caDdraftRediscount.set("qty", caDraftRediscountQty);
				caDdraftRediscount.set("amt", caDraftRediscountAmt);
				list.add(caDdraftRediscount);

				// 4.全部银行承兑汇票
				// 4.1签发
				Double baDraftIssueQty = (Double) getCellValue(row.getCell(22));
				Double baDdraftIssueAmt = (Double) getCellValue(row.getCell(23));
				Record baDraftIssue = new Record();
				baDraftIssue.set("area", area);
				baDraftIssue.set("organization", organization);
				baDraftIssue.set("year", year);
				baDraftIssue.set("month", month);
				baDraftIssue.set("bill_type", "baDraft");
				baDraftIssue.set("action_type", "issue");
				baDraftIssue.set("statistics_type", statisticsType);
				baDraftIssue.set("qty", baDraftIssueQty.intValue());
				baDraftIssue.set("amt", baDdraftIssueAmt);
				list.add(baDraftIssue);

				// 4.2承兑
				Double baDraftAcceptQty = (Double) getCellValue(row.getCell(24));
				Double baDdraftAcceptAmt = (Double) getCellValue(row.getCell(25));
				Record baDraftAccept = new Record();
				baDraftAccept.set("area", area);
				baDraftAccept.set("organization", organization);
				baDraftAccept.set("year", year);
				baDraftAccept.set("month", month);
				baDraftAccept.set("bill_type", "baDraft");
				baDraftAccept.set("action_type", "accept");
				baDraftAccept.set("statistics_type", statisticsType);
				baDraftAccept.set("qty", baDraftAcceptQty.intValue());
				baDraftAccept.set("amt", baDdraftAcceptAmt);
				list.add(baDraftAccept);

				// 2.3贴现
				Double baDraftDiscountQty = (Double) getCellValue(row.getCell(26));
				Double baDraftDiscountAmt = (Double) getCellValue(row.getCell(27));
				Record baDraftDiscount = new Record();
				baDraftDiscount.set("area", area);
				baDraftDiscount.set("organization", organization);
				baDraftDiscount.set("year", year);
				baDraftDiscount.set("month", month);
				baDraftDiscount.set("bill_type", "baDraft");
				baDraftDiscount.set("action_type", "discount");
				baDraftDiscount.set("statistics_type", statisticsType);
				baDraftDiscount.set("qty", baDraftDiscountQty);
				baDraftDiscount.set("amt", baDraftDiscountAmt);
				list.add(baDraftDiscount);
				// 4.4转贴现
				Double baDraftRediscountQty = (Double) getCellValue(row.getCell(28));
				Double baDraftRediscountAmt = (Double) getCellValue(row.getCell(29));
				Record baDraftRediscount = new Record();
				baDraftRediscount.set("area", area);
				baDraftRediscount.set("organization", organization);
				baDraftRediscount.set("year", year);
				baDraftRediscount.set("month", month);
				baDraftRediscount.set("bill_type", "baDraft");
				baDraftRediscount.set("action_type", "reDiscount");
				baDraftRediscount.set("statistics_type", statisticsType);
				baDraftRediscount.set("qty", baDraftRediscountQty);
				baDraftRediscount.set("amt", baDraftRediscountAmt);
				list.add(baDraftRediscount);
			}
			
			if(tips.size()!=0){
				throw new Exception(tips.toString());
			}
			dao.batchSaveBusinessData(list);
			dao.batchSaveClientData(clientList);
			dao.batchSaveBranchData(branchList);
			Record uploadedExcelRecord=new Record();
			uploadedExcelRecord.set("bank", organization);
			uploadedExcelRecord.set("year", year);
			uploadedExcelRecord.set("month", month);
			dao.saveUploadedExcel(uploadedExcelRecord);
			String tip=verifyService.verifyBranch(sheet, year, month, organization);
			Map<String,Object> result=new HashMap<String,Object>();
		    result.put("tip", tip);
		    result.put("order_id",uploadedExcelRecord.getLong("id"));
			return result;
	}

	private Object getCellValue(Cell cell) throws Exception {
		int cellType = cell.getCellType();
		switch (cellType) {
		case Cell.CELL_TYPE_STRING:// 文本
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_BLANK:// 空白
			return 0.0;
		case Cell.CELL_TYPE_NUMERIC:// 数字
			return cell.getNumericCellValue();
		case Cell.CELL_TYPE_FORMULA:
			return cell.getNumericCellValue();
		}
		return null;
	}

	public String generate(int year, int month) {
		FileInputStream fis;
		try {
			PropKit.use("appconfig.properties","UTF-8");
			String reportFolder=PropKit.get("reportFolder");
			fis = new FileInputStream(new File(PathKit.getWebRootPath() + "/WEB-INF/example/月报.xls"));
			HSSFWorkbook wb = new HSSFWorkbook(fis);
			produceRegulatoryTable(wb, year, month);// 生成监管表
			produceBankTable(wb, year, month);// 生成分行别统计表
			produceAreaTable(wb, year, month);// 生成分辖区统计表
			HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
			String fileName = "月报" + year +"-"+ month + ".xls";
			FileOutputStream fos = new FileOutputStream(new File(reportFolder + "/"+fileName));
			wb.write(fos);
			fis.close();
			fos.close();
			return fileName;
		} catch (FileNotFoundException e) {
			LOG.error(e.getMessage(), e);
		} catch (IOException e) {
			LOG.error(e.getMessage(), e);
		}
		return null;
	}

	// 生成地区报表
	private void produceAreaTable(HSSFWorkbook wb, int year, int month) {
		HSSFSheet sheet = wb.getSheetAt(0);
		List<Record> areas = dao.findAreas();
		for (int i = 0; i < areas.size(); i++) {
			// 银行网点情况
			int rowIndex = 5 + 2 * i;
			HSSFRow row = sheet.getRow(rowIndex);
			Record areaRecord = areas.get(i);
			String area = areaRecord.getStr("area");
			Record bankBranchInfo = dao.findBankBranchInfo(year, month, area);
			Cell BCell = row.getCell(1);
			BCell.setCellValue(bankBranchInfo.getBigDecimal("branch_qty").intValue());
			Cell CCell = row.getCell(2);
			CCell.setCellValue(bankBranchInfo.getBigDecimal("edraft_branch_qty").intValue());
			// 客户情况
			Record clientInfo = dao.findClientInfo(year, month, area);
			Cell ECell = row.getCell(4);
			ECell.setCellValue(clientInfo.getBigDecimal("euser_qty").intValue());
			Cell FCell = row.getCell(5);
			FCell.setCellValue(clientInfo.getBigDecimal("ebank_euser_qty").intValue());
			Cell GCell = row.getCell(6);
			GCell.setCellValue(clientInfo.getBigDecimal("draft_euser_qty").intValue());
			Cell HCell = row.getCell(7);
			HCell.setCellValue(clientInfo.getBigDecimal("edraft_euser_qty").intValue());
			Cell ICell = row.getCell(8);
			ICell.setCellValue(clientInfo.getBigDecimal("new_edraft_euser_qty").intValue());
		}

		// 本期数
		for (int i = 0; i < areas.size(); i++) {
			int rowIndex = 5 + 2 * i;
			HSSFRow row = sheet.getRow(rowIndex);
			Record areaRecord = areas.get(i);
			String area = areaRecord.getStr("area");
			// 电子商业承兑汇票
			// 1. 签发
			setCellValue(row, 19, 20, Constant.CAEDRAFT, Constant.CURRENT, Constant.ISSUE, year, month, area);
			// 2.贴现
			setCellValue(row, 21, 22, Constant.CAEDRAFT, Constant.CURRENT, Constant.DISCOUNT, year, month, area);
			// 3.转贴现
			setCellValue(row, 23, 24, Constant.CAEDRAFT, Constant.CURRENT, Constant.REDISCOUNT, year, month, area);
			// 电子银行承兑汇票
			// 1. 签发
			setCellValue(row, 25, 26, Constant.BAEDRAFT, Constant.CURRENT, Constant.ISSUE, year, month, area);
			// 2.承兑
			setCellValue(row, 27, 28, Constant.BAEDRAFT, Constant.CURRENT, Constant.ACCEPT, year, month, area);
			// 3.贴现
			setCellValue(row, 29, 30, Constant.BAEDRAFT, Constant.CURRENT, Constant.DISCOUNT, year, month, area);
			// 4.转贴现
			setCellValue(row, 31, 32, Constant.BAEDRAFT, Constant.CURRENT, Constant.REDISCOUNT, year, month, area);

			// 全部商业承兑汇票
			// 1. 签发
			setCellValue(row, 33, 34, Constant.CADRAFT, Constant.CURRENT, Constant.ISSUE, year, month, area);
			// 2.贴现
			setCellValue(row, 35, 36, Constant.CADRAFT, Constant.CURRENT, Constant.DISCOUNT, year, month, area);
			// 3.转贴现
			setCellValue(row, 37, 38, Constant.CADRAFT, Constant.CURRENT, Constant.REDISCOUNT, year, month, area);

			// 全部银行承兑汇票

			// 1. 签发
			setCellValue(row, 39, 40, Constant.BADRAFT, Constant.CURRENT, Constant.ISSUE, year, month, area);
			// 2.承兑
			setCellValue(row, 41, 42, Constant.BADRAFT, Constant.CURRENT, Constant.ACCEPT, year, month, area);
			// 3.贴现
			setCellValue(row, 43, 44, Constant.BADRAFT, Constant.CURRENT, Constant.DISCOUNT, year, month, area);
			// 3.转贴现
			setCellValue(row, 45, 46, Constant.BADRAFT, Constant.CURRENT, Constant.REDISCOUNT, year, month, area);

		}
		// 累计数
		for (int i = 0; i < areas.size(); i++) {
			int rowIndex = 6 + 2 * i;
			HSSFRow row = sheet.getRow(rowIndex);
			Record areaRecord = areas.get(i);
			String area = areaRecord.getStr("area");
			// 电子商业承兑汇票
			// 1. 签发
			setCellValue(row, 19, 20, Constant.CAEDRAFT, Constant.TOTAL, Constant.ISSUE, year, month, area);
			// 2.贴现
			setCellValue(row, 21, 22, Constant.CAEDRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month, area);
			// 3.转贴现
			setCellValue(row, 23, 24, Constant.CAEDRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month, area);
			// 电子银行承兑汇票
			// 1. 签发
			setCellValue(row, 25, 26, Constant.BAEDRAFT, Constant.TOTAL, Constant.ISSUE, year, month, area);
			// 2.承兑
			setCellValue(row, 27, 28, Constant.BAEDRAFT, Constant.TOTAL, Constant.ACCEPT, year, month, area);
			// 3.贴现
			setCellValue(row, 29, 30, Constant.BAEDRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month, area);
			// 4.转贴现
			setCellValue(row, 31, 32, Constant.BAEDRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month, area);

			// 全部商业承兑汇票
			// 1. 签发
			setCellValue(row, 33, 34, Constant.CADRAFT, Constant.TOTAL, Constant.ISSUE, year, month, area);
			// 2.贴现
			setCellValue(row, 35, 36, Constant.CADRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month, area);
			// 3.转贴现
			setCellValue(row, 37, 38, Constant.CADRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month, area);

			// 全部银行承兑汇票

			// 1. 签发
			setCellValue(row, 39, 40, Constant.BADRAFT, Constant.TOTAL, Constant.ISSUE, year, month, area);
			// 2.承兑
			setCellValue(row, 41, 42, Constant.BADRAFT, Constant.TOTAL, Constant.ACCEPT, year, month, area);
			// 3.贴现
			setCellValue(row, 43, 44, Constant.BADRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month, area);
			// 3.转贴现
			setCellValue(row, 45, 46, Constant.BADRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month, area);
		}

	}

	private void produceBankTable(HSSFWorkbook wb, int year, int month) {
		HSSFSheet sheet = wb.getSheetAt(1);
		List<Record> banks = dao.findBanks();
		for (int i = 0; i < 28; i++) {
			// 银行网点情况
			int rowIndex = 5 + 2 * i;
			HSSFRow row = sheet.getRow(rowIndex);
			Record bankRecord = banks.get(i);
			String bankName = bankRecord.getStr("organization");
			Record bankBranchInfo = dao.findBankBranchInfoByName(year, month, bankName);

			Cell BCell = row.getCell(1);
			BigDecimal branchQty = bankBranchInfo.getBigDecimal("branch_qty");
			if (branchQty == null) {
				continue;
			}
			BCell.setCellValue(branchQty.intValue());
			Cell CCell = row.getCell(2);
			CCell.setCellValue(bankBranchInfo.getBigDecimal("edraft_branch_qty").intValue());
			// 客户情况
			Record clientInfo = dao.findClientInfoByName(year, month, bankName);
			Cell ECell = row.getCell(4);
			ECell.setCellValue(clientInfo.getBigDecimal("euser_qty").intValue());
			Cell FCell = row.getCell(5);
			FCell.setCellValue(clientInfo.getBigDecimal("ebank_euser_qty").intValue());
			Cell GCell = row.getCell(6);
			GCell.setCellValue(clientInfo.getBigDecimal("draft_euser_qty").intValue());
			Cell HCell = row.getCell(7);
			HCell.setCellValue(clientInfo.getBigDecimal("edraft_euser_qty").intValue());
			Cell ICell = row.getCell(8);
			ICell.setCellValue(clientInfo.getBigDecimal("new_edraft_euser_qty").intValue());
		}

		// 农信系统
		Record agriRecord = dao.findAgriBankBranchInfo(year, month);
		HSSFRow row62 = sheet.getRow(61);
		Cell B62Cell = row62.getCell(1);
		BigDecimal branchQty = agriRecord.getBigDecimal("branch_qty");
		B62Cell.setCellValue(branchQty.intValue());
		Cell C62Cell = row62.getCell(2);
		C62Cell.setCellValue(agriRecord.getBigDecimal("edraft_branch_qty").intValue());
		// 客户情况
		Record agriClientInfo = dao.findArgiClientInfo(year, month);
		Cell E62Cell = row62.getCell(4);
		E62Cell.setCellValue(agriClientInfo.getBigDecimal("euser_qty").intValue());
		Cell F62Cell = row62.getCell(5);
		F62Cell.setCellValue(agriClientInfo.getBigDecimal("ebank_euser_qty").intValue());
		Cell G62Cell = row62.getCell(6);
		G62Cell.setCellValue(agriClientInfo.getBigDecimal("draft_euser_qty").intValue());
		Cell H62Cell = row62.getCell(7);
		H62Cell.setCellValue(agriClientInfo.getBigDecimal("edraft_euser_qty").intValue());
		Cell I62Cell = row62.getCell(8);
		I62Cell.setCellValue(agriClientInfo.getBigDecimal("new_edraft_euser_qty").intValue());
		// 村镇银行
		Record ruralRecord = dao.findRuralBankBranchInfo(year, month);
		HSSFRow row64 = sheet.getRow(63);
		Cell B64Cell = row64.getCell(1);
		B64Cell.setCellValue(ruralRecord.getBigDecimal("branch_qty").intValue());
		Cell C64Cell = row64.getCell(2);
		C64Cell.setCellValue(ruralRecord.getBigDecimal("edraft_branch_qty").intValue());
		// 客户情况
		Record ruralClientInfo = dao.findRuralClientInfo(year, month);
		Cell E64Cell = row64.getCell(4);
		E64Cell.setCellValue(ruralClientInfo.getBigDecimal("euser_qty").intValue());
		Cell F64Cell = row64.getCell(5);
		F64Cell.setCellValue(ruralClientInfo.getBigDecimal("ebank_euser_qty").intValue());
		Cell G64Cell = row64.getCell(6);
		G64Cell.setCellValue(ruralClientInfo.getBigDecimal("draft_euser_qty").intValue());
		Cell H64Cell = row64.getCell(7);
		H64Cell.setCellValue(ruralClientInfo.getBigDecimal("edraft_euser_qty").intValue());
		Cell I64Cell = row64.getCell(8);
		I64Cell.setCellValue(ruralClientInfo.getBigDecimal("new_edraft_euser_qty").intValue());

		// ----------------------------------------------------------------------------------//
		// 本期数
		for (int i = 0; i < 28; i++) {
			int rowIndex = 5 + 2 * i;
			HSSFRow row = sheet.getRow(rowIndex);
			Record bankRecord = banks.get(i);
			String bankName = bankRecord.getStr("organization");
			// 电子商业承兑汇票
			// 1. 签发
			setCellValueByBankName(row, 19, 20, Constant.CAEDRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
					bankName);
			// 2.贴现
			setCellValueByBankName(row, 21, 22, Constant.CAEDRAFT, Constant.CURRENT, Constant.DISCOUNT, year, month,
					bankName);
			// 3.转贴现
			setCellValueByBankName(row, 23, 24, Constant.CAEDRAFT, Constant.CURRENT, Constant.REDISCOUNT, year, month,
					bankName);
			// 电子银行承兑汇票
			// 1. 签发
			setCellValueByBankName(row, 25, 26, Constant.BAEDRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
					bankName);
			// 2.承兑
			setCellValueByBankName(row, 27, 28, Constant.BAEDRAFT, Constant.CURRENT, Constant.ACCEPT, year, month,
					bankName);
			// 3.贴现
			setCellValueByBankName(row, 29, 30, Constant.BAEDRAFT, Constant.CURRENT, Constant.DISCOUNT, year, month,
					bankName);
			// 4.转贴现
			setCellValueByBankName(row, 31, 32, Constant.BAEDRAFT, Constant.CURRENT, Constant.REDISCOUNT, year, month,
					bankName);

			// 全部商业承兑汇票
			// 1. 签发
			setCellValueByBankName(row, 33, 34, Constant.CADRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
					bankName);
			// 2.贴现
			setCellValueByBankName(row, 35, 36, Constant.CADRAFT, Constant.CURRENT, Constant.DISCOUNT, year, month,
					bankName);
			// 3.转贴现
			setCellValueByBankName(row, 37, 38, Constant.CADRAFT, Constant.CURRENT, Constant.REDISCOUNT, year, month,
					bankName);

			// 全部银行承兑汇票

			// 1. 签发
			setCellValueByBankName(row, 39, 40, Constant.BADRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
					bankName);
			// 2.承兑
			setCellValueByBankName(row, 41, 42, Constant.BADRAFT, Constant.CURRENT, Constant.ACCEPT, year, month,
					bankName);
			// 3.贴现
			setCellValueByBankName(row, 43, 44, Constant.BADRAFT, Constant.CURRENT, Constant.DISCOUNT, year, month,
					bankName);
			// 3.转贴现
			setCellValueByBankName(row, 45, 46, Constant.BADRAFT, Constant.CURRENT, Constant.REDISCOUNT, year, month,
					bankName);

		}
		// 农信系统
		HSSFRow agriCurrentRow = sheet.getRow(61);
		// 电子商业承兑汇票
		// 1. 签发
		setCellValueByClassify(agriCurrentRow, 19, 20, Constant.CAEDRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
				Constant.AGRI);
		// 2.贴现
		setCellValueByClassify(agriCurrentRow, 21, 22, Constant.CAEDRAFT, Constant.CURRENT, Constant.DISCOUNT, year,
				month, Constant.AGRI);
		// 3.转贴现
		setCellValueByClassify(agriCurrentRow, 23, 24, Constant.CAEDRAFT, Constant.CURRENT, Constant.REDISCOUNT, year,
				month, Constant.AGRI);
		// 电子银行承兑汇票
		// 1. 签发
		setCellValueByClassify(agriCurrentRow, 25, 26, Constant.BAEDRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
				Constant.AGRI);
		// 2.承兑
		setCellValueByClassify(agriCurrentRow, 27, 28, Constant.BAEDRAFT, Constant.CURRENT, Constant.ACCEPT, year,
				month, Constant.AGRI);
		// 3.贴现
		setCellValueByClassify(agriCurrentRow, 29, 30, Constant.BAEDRAFT, Constant.CURRENT, Constant.DISCOUNT, year,
				month, Constant.AGRI);
		// 4.转贴现
		setCellValueByClassify(agriCurrentRow, 31, 32, Constant.BAEDRAFT, Constant.CURRENT, Constant.REDISCOUNT, year,
				month, Constant.AGRI);
		// 全部商业承兑汇票
		// 1. 签发
		setCellValueByClassify(agriCurrentRow, 33, 34, Constant.CADRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
				Constant.AGRI);
		// 2.贴现
		setCellValueByClassify(agriCurrentRow, 35, 36, Constant.CADRAFT, Constant.CURRENT, Constant.DISCOUNT, year,
				month, Constant.AGRI);
		// 3.转贴现
		setCellValueByClassify(agriCurrentRow, 37, 38, Constant.CADRAFT, Constant.CURRENT, Constant.REDISCOUNT, year,
				month, Constant.AGRI);

		// 全部银行承兑汇票

		// 1. 签发
		setCellValueByClassify(agriCurrentRow, 39, 40, Constant.BADRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
				Constant.AGRI);
		// 2.承兑
		setCellValueByClassify(agriCurrentRow, 41, 42, Constant.BADRAFT, Constant.CURRENT, Constant.ACCEPT, year, month,
				Constant.AGRI);
		// 3.贴现
		setCellValueByClassify(agriCurrentRow, 43, 44, Constant.BADRAFT, Constant.CURRENT, Constant.DISCOUNT, year,
				month, Constant.AGRI);
		// 4.转贴现
		setCellValueByClassify(agriCurrentRow, 45, 46, Constant.BADRAFT, Constant.CURRENT, Constant.REDISCOUNT, year,
				month, Constant.AGRI);

		// 村镇银行
		HSSFRow ruralCurrentRow = sheet.getRow(63);
		// 电子商业承兑汇票
		// 1. 签发
		setCellValueByClassify(ruralCurrentRow, 19, 20, Constant.CAEDRAFT, Constant.CURRENT, Constant.ISSUE, year,
				month, Constant.RURAL);
		// 2.贴现
		setCellValueByClassify(ruralCurrentRow, 21, 22, Constant.CAEDRAFT, Constant.CURRENT, Constant.DISCOUNT, year,
				month, Constant.RURAL);
		// 3.转贴现
		setCellValueByClassify(ruralCurrentRow, 23, 24, Constant.CAEDRAFT, Constant.CURRENT, Constant.REDISCOUNT, year,
				month, Constant.RURAL);
		// 电子银行承兑汇票
		// 1. 签发
		setCellValueByClassify(ruralCurrentRow, 25, 26, Constant.BAEDRAFT, Constant.CURRENT, Constant.ISSUE, year,
				month, Constant.RURAL);
		// 2.承兑
		setCellValueByClassify(ruralCurrentRow, 27, 28, Constant.BAEDRAFT, Constant.CURRENT, Constant.ACCEPT, year,
				month, Constant.RURAL);
		// 3.贴现
		setCellValueByClassify(ruralCurrentRow, 29, 30, Constant.BAEDRAFT, Constant.CURRENT, Constant.DISCOUNT, year,
				month, Constant.RURAL);
		// 4.转贴现
		setCellValueByClassify(ruralCurrentRow, 31, 32, Constant.BAEDRAFT, Constant.CURRENT, Constant.REDISCOUNT, year,
				month, Constant.RURAL);
		// 全部商业承兑汇票
		// 1. 签发
		setCellValueByClassify(ruralCurrentRow, 33, 34, Constant.CADRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
				Constant.RURAL);
		// 2.贴现
		setCellValueByClassify(ruralCurrentRow, 35, 36, Constant.CADRAFT, Constant.CURRENT, Constant.DISCOUNT, year,
				month, Constant.RURAL);
		// 3.转贴现
		setCellValueByClassify(ruralCurrentRow, 37, 38, Constant.CADRAFT, Constant.CURRENT, Constant.REDISCOUNT, year,
				month, Constant.RURAL);

		// 全部银行承兑汇票

		// 1. 签发
		setCellValueByClassify(ruralCurrentRow, 39, 40, Constant.BADRAFT, Constant.CURRENT, Constant.ISSUE, year, month,
				Constant.RURAL);
		// 2.承兑
		setCellValueByClassify(ruralCurrentRow, 41, 42, Constant.BADRAFT, Constant.CURRENT, Constant.ACCEPT, year,
				month, Constant.RURAL);
		// 3.贴现
		setCellValueByClassify(ruralCurrentRow, 43, 44, Constant.BADRAFT, Constant.CURRENT, Constant.DISCOUNT, year,
				month, Constant.RURAL);
		// 4.转贴现
		setCellValueByClassify(ruralCurrentRow, 45, 46, Constant.BADRAFT, Constant.CURRENT, Constant.REDISCOUNT, year,
				month, Constant.RURAL);

		// 累计数
		for (int i = 0; i < 28; i++) {
			int rowIndex = 6 + 2 * i;
			HSSFRow row = sheet.getRow(rowIndex);
			Record bankRecord = banks.get(i);
			String bankName = bankRecord.getStr("organization");
			// 电子商业承兑汇票
			// 1. 签发
			setCellValueByBankName(row, 19, 20, Constant.CAEDRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
					bankName);
			// 2.贴现
			setCellValueByBankName(row, 21, 22, Constant.CAEDRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
					bankName);
			// 3.转贴现
			setCellValueByBankName(row, 23, 24, Constant.CAEDRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month,
					bankName);
			// 电子银行承兑汇票
			// 1. 签发
			setCellValueByBankName(row, 25, 26, Constant.BAEDRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
					bankName);
			// 2.承兑
			setCellValueByBankName(row, 27, 28, Constant.BAEDRAFT, Constant.TOTAL, Constant.ACCEPT, year, month,
					bankName);
			// 3.贴现
			setCellValueByBankName(row, 29, 30, Constant.BAEDRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
					bankName);
			// 4.转贴现
			setCellValueByBankName(row, 31, 32, Constant.BAEDRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month,
					bankName);

			// 全部商业承兑汇票
			// 1. 签发
			setCellValueByBankName(row, 33, 34, Constant.CADRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
					bankName);
			// 2.贴现
			setCellValueByBankName(row, 35, 36, Constant.CADRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
					bankName);
			// 3.转贴现
			setCellValueByBankName(row, 37, 38, Constant.CADRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month,
					bankName);

			// 全部银行承兑汇票

			// 1. 签发
			setCellValueByBankName(row, 39, 40, Constant.BADRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
					bankName);
			// 2.承兑
			setCellValueByBankName(row, 41, 42, Constant.BADRAFT, Constant.TOTAL, Constant.ACCEPT, year, month,
					bankName);
			// 3.贴现
			setCellValueByBankName(row, 43, 44, Constant.BADRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
					bankName);
			// 3.转贴现
			setCellValueByBankName(row, 45, 46, Constant.BADRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month,
					bankName);

		}

		// 农信系统
		HSSFRow agriTotalRow = sheet.getRow(62);
		// 电子商业承兑汇票
		// 1. 签发
		setCellValueByClassify(agriTotalRow, 19, 20, Constant.CAEDRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
				Constant.AGRI);
		// 2.贴现
		setCellValueByClassify(agriTotalRow, 21, 22, Constant.CAEDRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
				Constant.AGRI);
		// 3.转贴现
		setCellValueByClassify(agriTotalRow, 23, 24, Constant.CAEDRAFT, Constant.TOTAL, Constant.REDISCOUNT, year,
				month, Constant.AGRI);
		// 电子银行承兑汇票
		// 1. 签发
		setCellValueByClassify(agriTotalRow, 25, 26, Constant.BAEDRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
				Constant.AGRI);
		// 2.承兑
		setCellValueByClassify(agriTotalRow, 27, 28, Constant.BAEDRAFT, Constant.TOTAL, Constant.ACCEPT, year, month,
				Constant.AGRI);
		// 3.贴现
		setCellValueByClassify(agriTotalRow, 29, 30, Constant.BAEDRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
				Constant.AGRI);
		// 4.转贴现
		setCellValueByClassify(agriTotalRow, 31, 32, Constant.BAEDRAFT, Constant.TOTAL, Constant.REDISCOUNT, year,
				month, Constant.AGRI);
		// 全部商业承兑汇票
		// 1. 签发
		setCellValueByClassify(agriTotalRow, 33, 34, Constant.CADRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
				Constant.AGRI);
		// 2.贴现
		setCellValueByClassify(agriTotalRow, 35, 36, Constant.CADRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
				Constant.AGRI);
		// 3.转贴现
		setCellValueByClassify(agriTotalRow, 37, 38, Constant.CADRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month,
				Constant.AGRI);
		// 全部银行承兑汇票

		// 1. 签发
		setCellValueByClassify(agriTotalRow, 39, 40, Constant.BADRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
				Constant.AGRI);
		// 2.承兑
		setCellValueByClassify(agriTotalRow, 41, 42, Constant.BADRAFT, Constant.TOTAL, Constant.ACCEPT, year, month,
				Constant.AGRI);
		// 3.贴现
		setCellValueByClassify(agriTotalRow, 43, 44, Constant.BADRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
				Constant.AGRI);
		// 4.转贴现
		setCellValueByClassify(agriTotalRow, 45, 46, Constant.BADRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month,
				Constant.AGRI);

		// 村镇银行
		HSSFRow ruralTotalRow = sheet.getRow(64);
		// 电子商业承兑汇票
		// 1. 签发
		setCellValueByClassify(ruralTotalRow, 19, 20, Constant.CAEDRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
				Constant.RURAL);
		// 2.贴现
		setCellValueByClassify(ruralTotalRow, 21, 22, Constant.CAEDRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
				Constant.RURAL);
		// 3.转贴现
		setCellValueByClassify(ruralTotalRow, 23, 24, Constant.CAEDRAFT, Constant.TOTAL, Constant.REDISCOUNT, year,
				month, Constant.RURAL);
		// 电子银行承兑汇票
		// 1. 签发
		setCellValueByClassify(ruralTotalRow, 25, 26, Constant.BAEDRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
				Constant.RURAL);
		// 2.承兑
		setCellValueByClassify(ruralTotalRow, 27, 28, Constant.BAEDRAFT, Constant.TOTAL, Constant.ACCEPT, year, month,
				Constant.RURAL);
		// 3.贴现
		setCellValueByClassify(ruralTotalRow, 29, 30, Constant.BAEDRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
				Constant.RURAL);
		// 4.转贴现
		setCellValueByClassify(ruralTotalRow, 31, 32, Constant.BAEDRAFT, Constant.TOTAL, Constant.REDISCOUNT, year,
				month, Constant.RURAL);
		// 全部商业承兑汇票
		// 1. 签发
		setCellValueByClassify(ruralTotalRow, 33, 34, Constant.CADRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
				Constant.RURAL);
		// 2.贴现
		setCellValueByClassify(ruralTotalRow, 35, 36, Constant.CADRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
				Constant.RURAL);
		// 3.转贴现
		setCellValueByClassify(ruralTotalRow, 37, 38, Constant.CADRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month,
				Constant.RURAL);
		// 全部银行承兑汇票

		// 1. 签发
		setCellValueByClassify(ruralTotalRow, 39, 40, Constant.BADRAFT, Constant.TOTAL, Constant.ISSUE, year, month,
				Constant.RURAL);
		// 2.承兑
		setCellValueByClassify(ruralTotalRow, 41, 42, Constant.BADRAFT, Constant.TOTAL, Constant.ACCEPT, year, month,
				Constant.RURAL);
		// 3.贴现
		setCellValueByClassify(ruralTotalRow, 43, 44, Constant.BADRAFT, Constant.TOTAL, Constant.DISCOUNT, year, month,
				Constant.RURAL);
		// 4.转贴现
		setCellValueByClassify(ruralTotalRow, 45, 46, Constant.BADRAFT, Constant.TOTAL, Constant.REDISCOUNT, year, month,
				Constant.RURAL);
	}

	private void produceRegulatoryTable(HSSFWorkbook wb, int year, int month) {

	}

	private void setCellValue(HSSFRow row, int qtyCellNum, int amtCellNum, String billType, String statisticsType,
			String actionType, int year, int month, String area) {
		Record result = dao.findBusinessData(year, month, area, billType, statisticsType, actionType);
		Cell XCell = row.getCell(qtyCellNum);
		XCell.setCellValue(result.getBigDecimal("qty").intValue());
		Cell YCell = row.getCell(amtCellNum);
		YCell.setCellValue(result.getDouble("amt"));
	}

	private void setCellValueByBankName(HSSFRow row, int qtyCellNum, int amtCellNum, String billType,
			String statisticsType, String actionType, int year, int month, String bankName) {
		Record result = dao.findBusinessDataByBankName(year, month, bankName, billType, statisticsType, actionType);

		if (result.getBigDecimal("qty") != null) {
			Cell XCell = row.getCell(qtyCellNum);
			XCell.setCellValue(result.getBigDecimal("qty").intValue());
		}

		if (result.getDouble("amt") != null) {
			Cell YCell = row.getCell(amtCellNum);
			YCell.setCellValue(result.getDouble("amt"));
		}

	}

	private void setCellValueByClassify(HSSFRow row, int qtyCellNum, int amtCellNum, String billType,
			String statisticsType, String actionType, int year, int month, String classify) {
		Record result = dao.findBusinessDataByClassify(year, month, classify, billType, statisticsType, actionType);

		if (result.getBigDecimal("qty") != null) {
			Cell XCell = row.getCell(qtyCellNum);
			XCell.setCellValue(result.getBigDecimal("qty").intValue());
		}

		if (result.getDouble("amt") != null) {
			Cell YCell = row.getCell(amtCellNum);
			YCell.setCellValue(result.getDouble("amt"));
		}

	}

	
	

}
